package com.wechat.order.util.excelUtils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.jdom2.Attribute;
import org.jdom2.DataConversionException;
import org.jdom2.Element;
import java.util.List;
import java.util.ListIterator;

/**
 * excel导出属性设置
 * Created by zzy on 2017/12/30.
 */
public class ExcelProSetUtil {

    private final static int titleRowNum=0;

    private final static int theadRowNum=1;

    private final static int tbodyRowNum=2;

    private final static String NUMERIC="numeric";

    private final static String STRING="string";

    private final static String DATE="DATE";

    private final static String ENUM="ENUM";




    /**
     * excel导出设置列宽
     * @param sheet
     * @param colgroup
     */
    public static void setColumnWidth(HSSFSheet sheet,Element colgroup){
       List<Element> cols= colgroup.getChildren("col");

       for (ListIterator<Element> listIterator = cols.listIterator(); listIterator.hasNext();){
           int index= listIterator.nextIndex();
           Element col=listIterator.next();
           Attribute width=col.getAttribute("width");
           String unit=width.getValue().replaceAll("[0-9,\\.]","");//数字清除
           String value=width.getValue().replaceAll(unit,"");//单位清除

           int v=0;//宽度
           if(StringUtils.isBlank(unit)||"px".endsWith(unit)){
             v= Math.round(Float.parseFloat(value)*37F) ;
           }else if("em".endsWith(unit)){
             v= Math.round(Float.parseFloat(value)*267.5F) ;
           }
           sheet.setColumnWidth(index,v);
       }
    }

    /**
     * 设置标题
     * @param workbook
     * @param sheet
     * @param title
     */
    public static void setSheetTitle(HSSFWorkbook workbook, HSSFSheet sheet, Element title) throws DataConversionException {
        List<Element> trs=title.getChildren("tr");
        for (ListIterator<Element> listIterator = trs.listIterator(); listIterator.hasNext();){
            Element tr=listIterator.next();
            List<Element> tds=tr.getChildren("td");
            HSSFRow row=sheet.createRow(titleRowNum);
            HSSFCellStyle cellStyle=workbook.createCellStyle();
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//单元格居中
            for (Element td:tds) {
                HSSFCell cell=row.createCell(0);
                Attribute rowSpan=td.getAttribute("rowspan");
                Attribute colsSpan=td.getAttribute("colspan");
                Attribute value=td.getAttribute("value");
                if(value!=null){
                    String val=value.getValue();
                    cell.setCellValue(val);
                    //设置字体
                    HSSFFont font=workbook.createFont();
                    font.setFontName("仿宋_GB2312");
                    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
                    font.setFontHeightInPoints((short) 12);
                    cellStyle.setFont(font);
                    cell.setCellStyle(cellStyle);
                    //合并单元格
                    sheet.addMergedRegion(new CellRangeAddress(0,rowSpan.getIntValue()-1,0,colsSpan.getIntValue()-1));
                }

            }

        }
    }

    /**
     * 设置表头
     * @param sheet
     * @param thead
     */
    public static void setSheetThead(HSSFSheet sheet, Element thead) {
        List<Element> trs=thead.getChildren("tr");
        for (Element tr:trs){
            HSSFRow row=sheet.createRow(theadRowNum);
            List<Element> ths=tr.getChildren("th");
            for (ListIterator<Element> iter=ths.listIterator();iter.hasNext();) {
                int thIndex=iter.nextIndex();
                Element th=iter.next();
                Attribute valueAttr=th.getAttribute("value");
                HSSFCell cell=row.createCell(thIndex);
                if(valueAttr!=null){
                    String value=valueAttr.getValue();
                    cell.setCellValue(value);
                }
            }
        }
    }

    /**
     * 设置数据区域属性
     * @param workbook
     * @param sheet
     * @param tbody
     */
    public static void setSheetTbody(HSSFWorkbook workbook, HSSFSheet sheet, Element tbody) throws DataConversionException {
         Element tr=tbody.getChild("tr");
         int repeat=tr.getAttribute("repeat").getIntValue();
         List<Element> tds=tr.getChildren("td");
         for (int i=0;i<repeat;i++){
            HSSFRow row=sheet.createRow(tbodyRowNum+i);
            for (int column=0;column<tds.size();column++){
                Element td=tds.get(column);
                HSSFCell cell=row.createCell(column);
                setType(workbook,cell,td);
            }
         }

    }

    /**
     * 设置单元格样式
     * @param workbook
     * @param cell
     * @param td
     */
    private static void setType(HSSFWorkbook workbook, HSSFCell cell, Element td) {
        Attribute typeAttr=td.getAttribute("type");
        String type=typeAttr.getValue();
        HSSFDataFormat format=workbook.createDataFormat();
        HSSFCellStyle cellStyle=workbook.createCellStyle();
        if(NUMERIC.equalsIgnoreCase(type)){
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            Attribute formatAttr=td.getAttribute("format");
            String formatValue=formatAttr.getValue();
            formatValue= StringUtils.isNotBlank(formatValue)?formatValue:"#,##0.00";
            cellStyle.setDataFormat(format.getFormat(formatValue));
        }else if (STRING.equalsIgnoreCase(type)){
            cell.setCellValue("");
            cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            cellStyle.setDataFormat(format.getFormat("@"));
        }else if (DATE.equalsIgnoreCase(type)){
            cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
            cellStyle.setDataFormat(format.getFormat("yyyy-m-d"));
        }else if (ENUM.equalsIgnoreCase(type)){
            CellRangeAddressList rangeAddressList=new CellRangeAddressList(cell.getRowIndex(),
                    cell.getRowIndex(),
                    cell.getColumnIndex(),cell.getColumnIndex());
            Attribute enumAttr=td.getAttribute("format");
            String enumValue=enumAttr.getValue();
            DVConstraint constraint=DVConstraint.createExplicitListConstraint(enumValue.split(","));
            //数据有效性对象
            HSSFDataValidation dataValidation=new HSSFDataValidation(rangeAddressList,constraint);
            workbook.getSheetAt(0).addValidationData(dataValidation);
        }
        cell.setCellStyle(cellStyle);
    }
}
